package com.jxmdkt.common;
import java.io.ByteArrayOutputStream;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Matcher;
import java.util.regex.Pattern;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

import com.jxmdkt.model.UinfoModel;

public class ExcelUtils {
    public static byte[] createExcel(List<UinfoModel> list) throws WriteException,IOException{
        //创建工作薄
	    String[] title=new String[]{"卡号","收货人","地址","联系电话","属地","录入时间","备注","快递公司","快递单号"};
	    int columnBestWidth[]=new  int[title.length];  
	    ByteArrayOutputStream os =new ByteArrayOutputStream();
        WritableWorkbook workbook = Workbook.createWorkbook(os);
        //创建新的一页
        WritableSheet sheet = workbook.createSheet("First Sheet",0);
       
        for(int j=0;j<title.length;j++){
		sheet.addCell(new Label(j,0,title[j]));
		int width=title[j].length()+getChineseNum(title[j]);  
                if(columnBestWidth[j]<width)    
                    columnBestWidth[j]=width;
	}
        for(int i=0;i<list.size();i++){
        	int width=0;
        	UinfoModel model=list.get(i);

        	List<String> arr=new ArrayList<String>();
        	arr.add(model.getStr("cardno"));
        	arr.add(model.getStr("name"));
        	arr.add(model.getStr("address")==null?"":model.getStr("address"));
        	arr.add(model.getStr("phoneno")==null?"":model.getStr("phoneno"));
        	arr.add(model.getStr("cardcomment")==null?"":model.getStr("cardcomment"));
        	arr.add(model.getStr("datetime")==null?"":model.getStr("datetime"));
        	arr.add(model.getStr("comment")==null?"":model.getStr("comment"));
        	arr.add(model.getStr("delivercp")==null?"":model.getStr("delivercp"));
        	arr.add(model.getStr("deliverno")==null?"":model.getStr("deliverno"));
        	for(int j=0;j<arr.size();j++){
        		sheet.addCell(new Label(j,i+1,arr.get(j)));
        		width=arr.get(j).length()+getChineseNum(arr.get(j));  
                        if(columnBestWidth[j]<width)    
                            columnBestWidth[j]=width;
        	}
        }
        for(int i=0;i<columnBestWidth.length;i++){    ///设置每列宽
                sheet.setColumnView(i, columnBestWidth[i]+10);
        }
        
        workbook.write();
        workbook.close();
        os.flush();
        byte[] bs =os.toByteArray();
        System.out.println(bs.length);
        os.close();
        return bs;
    }
    public static List<UinfoModel>  readDeliverExcel(File file) throws Exception{   

	        Workbook wb=null;  
	        List<UinfoModel> models=new ArrayList<UinfoModel>();
	        try {  

	            wb=Workbook.getWorkbook(file);  
	            if(wb!=null){  

	                Sheet[] sheets = wb.getSheets();   
	                if(sheets!=null&&sheets.length!=0){  
	                    for(int i=0;i<sheets.length;i++){  
	  
	                        Sheet sheet=wb.getSheet(i);  
	                        int rows_len=sheet.getRows();  
	                        for(int j=1;j<rows_len;j++){
	                        	UinfoModel model=new UinfoModel();
	                            Cell[] cells=sheet.getRow(j);  
	                            if(cells!=null&&cells.length>=8){  
	                        	if(isNotEmpty(cells[0].getContents(), cells[1].getContents())){
		                        	model.set("cardno", cells[0].getContents());
		                        	model.set("delivercp", cells[7].getContents());
		                        	model.set("deliverno", cells[8].getContents());
		                        	model.set("delivered","1");
		                        	models.add(model);
	                        	}else {
	                        		throw new Exception("文件格式不对");
					}
	                            } else {
	                        	    throw new Exception("文件格式不对");
	                            }
	                        }  
	                    }  
	                }  
	            }  
	        } catch (BiffException e) {  
	            throw e;
	        } catch (IOException e) {  
	        	throw e;
	        }finally {    
	            wb.close();    
	        }    
	        return models;  
	    }  
    
    public static List<UinfoModel>  readExcel(File file) throws Exception{   

	        Workbook wb=null;  
	        List<UinfoModel> models=new ArrayList<UinfoModel>();
	        try {  

	            wb=Workbook.getWorkbook(file);  
	            if(wb!=null){  

	                Sheet[] sheets = wb.getSheets();   
	                if(sheets!=null&&sheets.length!=0){  
	                    for(int i=0;i<sheets.length;i++){  
	  
	                        Sheet sheet=wb.getSheet(i);  
	                        int rows_len=sheet.getRows();  
	                        for(int j=1;j<rows_len;j++){
	                        	UinfoModel model=new UinfoModel();
	                            Cell[] cells=sheet.getRow(j);  
	                            if(cells!=null&&cells.length>=2){  
	                        	if(isNotEmpty(cells[0].getContents(), cells[1].getContents())){
	                        	model.set("cardno", cells[0].getContents());
	                        	model.set("password", cells[1].getContents());
	                        	if(cells.length==3)
	                        	model.set("cardcomment", cells[2].getContents()==null?"":cells[2].getContents());
	                        	models.add(model);
	                        	}else {
	                        		throw new Exception("文件格式不对");
					}
	                            } else {
	                        	    throw new Exception("文件格式不对");
	                            }
	                        }  
	                    }  
	                }  
	            }  
	        } catch (BiffException e) {  
	            throw e;
	        } catch (IOException e) {  
	        	throw e;
	        }finally {    
	            wb.close();    
	        }    
	        return models;  
	    }  
    private static boolean isNotEmpty(String cardno,String password){
	    boolean b =true;
	    if(cardno==null||cardno.equals(""))
		    b=false;
	    if(password==null||password.equals(""))
		    b=false;
	    return b;
    }
    public static int getChineseNum(String context){    ///统计context中是汉字的个数
	        int lenOfChinese=0;
	        Pattern p = Pattern.compile("[\u4e00-\u9fa5]");    //汉字的Unicode编码范围
	        Matcher m = p.matcher(context);
	        while(m.find()){
	            lenOfChinese++;
	        }
	        return lenOfChinese;
 }
}